package com.threeonemeeting.db;

import java.io.ByteArrayOutputStream;
import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;

import com.threeonemeeting.list.CityInfoList;
import com.threeonemeeting.list.FullMeetingInfoList;
import com.threeonemeeting.list.TradesInfoList;
import com.threeonemeeting.list.UserInfoList;
import com.threeonemeeting.model.CityInfo;
import com.threeonemeeting.model.DepartmentInfo;
import com.threeonemeeting.model.DownloadInfo;
import com.threeonemeeting.model.DownloadInfoList;
import com.threeonemeeting.model.FullMeetingInfo;
import com.threeonemeeting.model.MeetingTradesInfo;
import com.threeonemeeting.model.PosInfo;
import com.threeonemeeting.model.UserInfo;

public class AppDBHelper {

	public static final String DATABASE_NAME = "31meeting.db";
	public static final int DATABASE_VERSION = 4;

	/** table */
	public static final String TABLE_IMAGE = "t_image"; // 描述图片的一些信息
	public static final String TABLE_DOWNLOAD = "t_downloaded";
	public static final String TABLE_ACCOUNT = "t_account";
	public static final String TABLE_TRADE = "t_trades";
	public static final String TABLE_CITY = "t_city";
	public static final String TABLE_POS = "t_posstatus";
	public static final String TABLE_DEPT = "t_dept";
	public static final String TABLE_MEETING = "t_meeting";

	/** table row */

	/** image */
	public static final String IMAGE_KEY_URL = "imageurl"; // 图片的URL
	public static final String IMAGE_KEY_IMAGEBLOB = "imageblob"; // 图片的二进制数据
	public static final String IMAGE_KEY_IMAGESIZE = "imagesize"; // 图片的大小

	/** downloaded */
	public static final String DOWNLOAD_KEY_ID = "id";
	public static final String DOWNLOAD_KEY_APPKEY = "appkey";
	public static final String DOWNLOAD_KEY_APPTITLE = "apptitle"; // 软件名称
	public static final String DOWNLOAD_KEY_FILENAME = "filename"; // 软件名称
	public static final String DOWNLOAD_KEY_FILESIZE = "filesize"; // 下载文件的总的大小
	public static final String DOWNLOAD_KEY_DOWNLOADED_SIZE = "downloadedsize"; // 文件已经下载的大小
	public static final String DOWNLOAD_KEY_DOWNDATE = "downloaddate"; // 下载的日期
	public static final String DOWNLOAD_KEY_FILEURL = "fileurl"; // 文件的远端地址
	public static final String DOWNLOAD_KEY_FILEPATH = "filepath"; // 文件存储的本地路径
	public static final String DOWNLOAD_KEY_STATUS = "status"; // 当前的状

	/** account */
	public static final String ACCOUNT_KEY_ID = "userid"; // 主帐号的ID
	public static final String ACCOUNT_KEY_MAIN_ID = "main_userid"; // 关联主帐号的ID，如果本身就是主帐号，则为0
	public static final String ACCOUNT_KEY_PASSWORD = "password"; // 主帐号的ID
	public static final String ACCOUNT_KEY_REALNAME = "realname";
	public static final String ACCOUNT_KEY_NICKNAME = "nickname";
	public static final String ACCOUNT_KEY_EMAIL = "email";
	public static final String ACCOUNT_KEY_MOBILE = "mobile";
	public static final String ACCOUNT_KEY_ADDRESS = "address";
	public static final String ACCOUNT_KEY_COMPANY = "company";
	public static final String ACCOUNT_KEY_GENDER = "gender";
	public static final String ACCOUNT_KEY_TRADE = "trade";
	public static final String ACCOUNT_KEY_DEPT = "dept";
	public static final String ACCOUNT_KEY_POSSTATUS = "posstatus";
	public static final String ACCOUNT_KEY_COIN = "coin"; // 积分
	public static final String ACCOUNT_KEY_CHARGE = "charge"; // 余额
	public static final String ACCOUNT_KEY_LASTLOGIN = "lastlogin"; // 上次最近一次登录

	//
	// public static final String ACCOUNT_KEY_MAIN_ID = "attach_userid"; //
	// 关联主帐号的ID
	// public static final String ACCOUNT_KEY_ATTACH_PASSWORD =
	// "attach_password"; // 改帐号的密码
	// public static final String ACCOUNT_KEY_ATTACH_REALNAME =
	// "attach_realname"; // 该帐号下关联的其余帐号
	// public static final String ACCOUNT_KEY_ATTACH_NICKNAME =
	// "attach_nickname"; // 该帐号下关联的其余帐号
	// public static final String ACCOUNT_KEY_ATTACH_EMAIL = "attach_email"; //
	// public static final String ACCOUNT_KEY_ATTACH_MOBILE = "attach_mobile";
	// //
	// public static final String ACCOUNT_KEY_ATTACH_ADDRESS = "attach_address";
	// //
	// public static final String ACCOUNT_KEY_ATTACH_COMPANY = "attach_company";
	// //
	// public static final String ACCOUNT_KEY_ATTACH_GENDER = "attach_gender";
	// //
	// public static final String ACCOUNT_KEY_ATTACH_TRADE = "attach_trade"; //
	// public static final String ACCOUNT_KEY_ATTACH_DEPT = "attach_dept"; //
	// public static final String ACCOUNT_KEY_ATTACH_POSSTATUS =
	// "attach_posstatus"; //
	// public static final String ACCOUNT_KEY_ATTACH_COIN = "attach_coin"; // 积分
	// public static final String ACCOUNT_KEY_ATTACH_CHARGE = "attach_charge";
	// // 余额
	// public static final String ACCOUNT_KEY_ATTACH_LASTLOGIN =
	// "attach_lastlogin"; // 上次最近一次登录

	/** trades */
	public static final String TRADE_KEY_ID = "id";
	public static final String TRADE_KEY_NAME = "name";

	/** city */
	public static final String CITY_KEY_ID = "id";
	public static final String CITY_KEY_PROVINCEID = "provinceid";
	public static final String CITY_KEY_CITYID = "cityid";
	public static final String CITY_KEY_NAME = "name";

	/** 职位 */
	public static final String POS_KEY_ID = "id";
	public static final String POS_KEY_NAME = "name";

	/** 部门 */
	public static final String DEPT_KEY_ID = "id";
	public static final String DEPT_KEY_NAME = "name";

	/** 会议 */
	public static final String MEETING_KEY_ID = "id";
	public static final String MEETING_KEY_TITLE = "title";
	public static final String MEETING_KEY_LOGO = "logo";
	public static final String MEETING_KEY_ADDRESS = "address";
	public static final String MEETING_KEY_BEGINTIME = "begintime";
	public static final String MEETING_KEY_ENDTIME = "endtime";
	public static final String MEETING_KEY_SUMMARY = "summary";
	public static final String MEETING_KEY_TYPE = "type"; // 收费还是免费
	public static final String MEETING_KEY_JONILIMITTIME = "jonilimittime";
	public static final String MEETING_KEY_PRICE = "price";
	public static final String MEETING_KEY_LATITUTE = "latitute";
	public static final String MEETING_KEY_LONGITUTE = "longitute";
	public static final String MEETING_KEY_ISCLOSETOPIC = "closetopic";
	public static final String MEETING_KEY_ISCLOSEJOINE = "closejoin";
	public static final String MEETING_KEY_ISJOINED = "isjoined";
	public static final String MEETING_KEY_ISFAVED = "isfaved";
	public static final String MEETING_KEY_DOCUMENTS = "docnum"; // 文档的数目
	public static final String MEETING_KEY_VISIT_TIME = "visittime"; // 最后访问时间

	/** COL */

	/** download col */
	private static final String[] DOWNLOAD_COLS = new String[] {
			DOWNLOAD_KEY_ID, DOWNLOAD_KEY_APPKEY, DOWNLOAD_KEY_APPTITLE,
			DOWNLOAD_KEY_FILENAME, DOWNLOAD_KEY_FILESIZE,
			DOWNLOAD_KEY_DOWNDATE, DOWNLOAD_KEY_DOWNLOADED_SIZE,
			DOWNLOAD_KEY_FILEURL, DOWNLOAD_KEY_FILEPATH, DOWNLOAD_KEY_STATUS };

	/** image col */
	private static final String[] IMAGE_COLS = new String[] { IMAGE_KEY_URL,
			IMAGE_KEY_IMAGEBLOB, IMAGE_KEY_IMAGESIZE };

	/** account col */
	private static final String[] ACCOUNT_COLS = new String[] { ACCOUNT_KEY_ID,
			ACCOUNT_KEY_MAIN_ID, ACCOUNT_KEY_PASSWORD, ACCOUNT_KEY_REALNAME,
			ACCOUNT_KEY_NICKNAME, ACCOUNT_KEY_EMAIL, ACCOUNT_KEY_MOBILE,
			ACCOUNT_KEY_ADDRESS, ACCOUNT_KEY_COMPANY, ACCOUNT_KEY_GENDER,
			ACCOUNT_KEY_TRADE, ACCOUNT_KEY_DEPT, ACCOUNT_KEY_POSSTATUS,
			ACCOUNT_KEY_COIN, ACCOUNT_KEY_CHARGE, ACCOUNT_KEY_LASTLOGIN };

	/** trades col */
	private static final String[] TRADE_COLS = new String[] { TRADE_KEY_ID,
			TRADE_KEY_NAME };

	/** trades col */
	private static final String[] CITY_COLS = new String[] { CITY_KEY_ID,
			CITY_KEY_PROVINCEID, CITY_KEY_CITYID, CITY_KEY_NAME };

	/** pos col */
	private static final String[] POS_COLS = new String[] { POS_KEY_ID,
			POS_KEY_NAME };

	/** dept col */
	private static final String[] DEPT_COLS = new String[] { DEPT_KEY_ID,
			DEPT_KEY_NAME };

	/** meeting col */
	private static final String[] MEETING_COLS = new String[] { MEETING_KEY_ID,
			MEETING_KEY_TITLE, MEETING_KEY_LOGO, MEETING_KEY_ADDRESS,
			MEETING_KEY_BEGINTIME, MEETING_KEY_ENDTIME, MEETING_KEY_SUMMARY,
			MEETING_KEY_TYPE, MEETING_KEY_JONILIMITTIME, MEETING_KEY_PRICE,
			MEETING_KEY_LATITUTE, MEETING_KEY_LONGITUTE,
			MEETING_KEY_ISCLOSETOPIC, MEETING_KEY_ISCLOSEJOINE,
			MEETING_KEY_ISJOINED, MEETING_KEY_ISFAVED, MEETING_KEY_ISFAVED };

	/** create sql */

	/** create download */
	private static final String TABLE_IMAGE_CREATE_SQL = "CREATE TABLE IF NOT EXISTS  "
			+ TABLE_IMAGE
			+ "( "
			+ IMAGE_KEY_URL
			+ " VARCHAR(1024) NOT NULL, "
			+ IMAGE_KEY_IMAGEBLOB
			+ " BLOB , "
			+ IMAGE_KEY_IMAGESIZE
			+ " INTEGER )";

	/** create download */
	private static final String TABLE_DOWNLOAD_CREATE_SQL = "CREATE TABLE IF NOT EXISTS  "
			+ TABLE_DOWNLOAD
			+ "( "
			+ DOWNLOAD_KEY_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ DOWNLOAD_KEY_APPKEY
			+ " VARCHAR(256) NOT NULL, "
			+ DOWNLOAD_KEY_APPTITLE
			+ " VARCHAR(256) NOT NULL, "
			+ DOWNLOAD_KEY_FILENAME
			+ " VARCHAR(256) NOT NULL, "
			+ DOWNLOAD_KEY_FILESIZE
			+ " INTEGER, "
			+ DOWNLOAD_KEY_DOWNLOADED_SIZE
			+ " INTEGER, "
			+ DOWNLOAD_KEY_DOWNDATE
			+ " INTEGER, "
			+ DOWNLOAD_KEY_FILEURL
			+ " VARCHAR(256), "
			+ DOWNLOAD_KEY_FILEPATH
			+ " VARCHAR(256),"
			+ DOWNLOAD_KEY_STATUS + " INTEGER" + ")";

	/** create account */
	private static final String TABLE_ACCOUNT_CREATE_SQL = "CREATE TABLE IF NOT EXISTS  "
			+ TABLE_ACCOUNT
			+ "( "
			+ ACCOUNT_KEY_ID
			+ " INTEGER,"
			+ ACCOUNT_KEY_MAIN_ID
			+ " INTEGER NOT NULL, "
			+ ACCOUNT_KEY_PASSWORD
			+ " VARCHAR(256) NOT NULL, "
			+ ACCOUNT_KEY_REALNAME
			+ " VARCHAR(256), "
			+ ACCOUNT_KEY_NICKNAME
			+ " VARCHAR(256), "
			+ ACCOUNT_KEY_EMAIL
			+ " VARCHAR(256), "
			+ ACCOUNT_KEY_MOBILE
			+ " VARCHAR(20), "
			+ ACCOUNT_KEY_ADDRESS
			+ " VARCHAR(256), "
			+ ACCOUNT_KEY_COMPANY
			+ " VARCHAR(256) NOT NULL, "
			+ ACCOUNT_KEY_GENDER
			+ " INTEGER, "
			+ ACCOUNT_KEY_TRADE
			+ " INTEGER, "
			+ ACCOUNT_KEY_DEPT
			+ " INTEGER, "
			+ ACCOUNT_KEY_POSSTATUS
			+ " INTEGER, "
			+ ACCOUNT_KEY_COIN
			+ " REALTIME, "
			+ ACCOUNT_KEY_CHARGE
			+ " REALTIME, "
			+ ACCOUNT_KEY_LASTLOGIN
			+ " VARCHAR(256) NOT NULL "
			+ ")";

	/** create trades */
	private static final String TABLE_TRADE_CREATE_SQL = "CREATE TABLE IF NOT EXISTS  "
			+ TABLE_TRADE
			+ "( "
			+ TRADE_KEY_ID
			+ " INTEGER,"
			+ TRADE_KEY_NAME
			+ " VARCHAR(256) NOT NULL" + ")";

	/** create city */
	private static final String TABLE_CITY_CREATE_SQL = "CREATE TABLE IF NOT EXISTS  "
			+ TABLE_CITY
			+ "( "
			+ CITY_KEY_ID
			+ " INTEGER,"
			+ CITY_KEY_PROVINCEID
			+ " INTEGER,"
			+ CITY_KEY_CITYID
			+ " INTEGER,"
			+ CITY_KEY_NAME + " VARCHAR(256) NOT NULL" + ")";

	/** create POS */
	private static final String TABLE_POS_CREATE_SQL = "CREATE TABLE IF NOT EXISTS  "
			+ TABLE_POS
			+ "( "
			+ POS_KEY_ID
			+ " INTEGER,"
			+ POS_KEY_NAME
			+ " VARCHAR(256) NOT NULL" + ")";

	/** create Dept */
	private static final String TABLE_DEPT_CREATE_SQL = "CREATE TABLE IF NOT EXISTS  "
			+ TABLE_DEPT
			+ "( "
			+ DEPT_KEY_ID
			+ " INTEGER,"
			+ DEPT_KEY_NAME
			+ " VARCHAR(256) NOT NULL" + ")";

	/** create Dept */
	private static final String TABLE_MEETING_CREATE_SQL = "CREATE TABLE IF NOT EXISTS  "
			+ TABLE_MEETING
			+ "( "
			+ MEETING_KEY_ID
			+ " INTEGER,"
			+ MEETING_KEY_TITLE
			+ " VARCHAR(256) NOT NULL,"
			+ MEETING_KEY_LOGO
			+ " VARCHAR(1024) NOT NULL,"
			+ MEETING_KEY_ADDRESS
			+ " VARCHAR(1024) NOT NULL,"
			+ MEETING_KEY_BEGINTIME
			+ " VARCHAR(32) NOT NULL,"
			+ MEETING_KEY_ENDTIME
			+ " VARCHAR(32) NOT NULL,"
			+ MEETING_KEY_SUMMARY
			+ " VARCHAR(4096) NOT NULL,"
			+ MEETING_KEY_TYPE
			+ " VARCHAR(32) NOT NULL,"
			+ MEETING_KEY_JONILIMITTIME
			+ " VARCHAR(32) NOT NULL,"
			+ MEETING_KEY_PRICE
			+ " REALTIME,"
			+ MEETING_KEY_LATITUTE
			+ " REALTIME,"
			+ MEETING_KEY_LONGITUTE
			+ " REALTIME,"
			+ MEETING_KEY_ISCLOSETOPIC
			+ " INTEGER,"
			+ MEETING_KEY_ISCLOSEJOINE
			+ " INTEGER,"
			+ MEETING_KEY_ISJOINED
			+ " INTEGER,"
			+ MEETING_KEY_ISFAVED
			+ " INTEGER,"
			+ MEETING_KEY_DOCUMENTS
			+ " INTEGER,"
			+ MEETING_KEY_VISIT_TIME
			+ " INTEGER" + ")";

	/** drop databases */
	private static final String TABLE_DOWNLOAD_DELETE_SQL = "DELETE FROM "
			+ TABLE_DOWNLOAD;

	private static final String TABLE_IMAGE_DELETE_SQL = "DELETE FROM "
			+ TABLE_IMAGE;

	private static final String TABLE_ACCOUNT_DELETE_SQL = "DELETE FROM "
			+ TABLE_ACCOUNT;

	private static final String TABLE_TRADE_DELETE_SQL = "DELETE FROM "
			+ TABLE_TRADE;

	private static final String TABLE_CITY_DELETE_SQL = "DELETE FROM "
			+ TABLE_CITY;

	private static final String TABLE_POS_DELETE_SQL = "DELETE FROM "
			+ TABLE_POS;

	private static final String TABLE_DEPT_DELETE_SQL = "DELETE FROM "
			+ TABLE_DEPT;

	private static final String TABLE_MEETING_DELETE_SQL = "DELETE FROM "
			+ TABLE_MEETING;

	private SQLiteDatabase db;
	private final DBOpenHelper dbOpenHelper;

	public AppDBHelper(final Context context) {
		this.dbOpenHelper = new DBOpenHelper(context);
		open();
	}

	public boolean isOpen() {
		return this.db.isOpen();
	}

	private void open() {
		this.db = null;
		if (dbOpenHelper != null) {
			this.db = this.dbOpenHelper.getWritableDatabase();
		}
	}

	public void close() {
		if (this.db != null) {
			this.db.close();
		}
	}

	// 删除数据库

	public void deleteTables() {
		try {
			db.execSQL(TABLE_IMAGE_DELETE_SQL);
			db.execSQL(TABLE_DOWNLOAD_DELETE_SQL);
			db.execSQL(TABLE_ACCOUNT_DELETE_SQL);
			db.execSQL(TABLE_TRADE_DELETE_SQL);
			db.execSQL(TABLE_CITY_DELETE_SQL);
			db.execSQL(TABLE_POS_DELETE_SQL);
			db.execSQL(TABLE_DEPT_DELETE_SQL);
			db.execSQL(TABLE_MEETING_DELETE_SQL);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/** DB辅助类 */
	private static class DBOpenHelper extends SQLiteOpenHelper {

		public DBOpenHelper(final Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(final SQLiteDatabase db) {
			try {
				db.execSQL(TABLE_IMAGE_CREATE_SQL);
				db.execSQL(TABLE_DOWNLOAD_CREATE_SQL);
				db.execSQL(TABLE_ACCOUNT_CREATE_SQL);
				db.execSQL(TABLE_TRADE_CREATE_SQL);
				db.execSQL(TABLE_CITY_CREATE_SQL);
				db.execSQL(TABLE_POS_CREATE_SQL);
				db.execSQL(TABLE_DEPT_CREATE_SQL);
				db.execSQL(TABLE_MEETING_CREATE_SQL);

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		@Override
		public void onOpen(final SQLiteDatabase db) {
			super.onOpen(db);

		}

		@Override
		public void onUpgrade(final SQLiteDatabase db, final int oldVersion,
				final int newVersion) {
			try {
				db.execSQL("DROP TABLE IF EXISTS " + TABLE_IMAGE);
				db.execSQL("DROP TABLE IF EXISTS " + TABLE_DOWNLOAD);
				db.execSQL("DROP TABLE IF EXISTS " + TABLE_ACCOUNT);
				db.execSQL("DROP TABLE IF EXISTS " + TABLE_TRADE);
				db.execSQL("DROP TABLE IF EXISTS " + TABLE_CITY);
				db.execSQL("DROP TABLE IF EXISTS " + TABLE_POS);
				db.execSQL("DROP TABLE IF EXISTS " + TABLE_DEPT);
				db.execSQL("DROP TABLE IF EXISTS " + TABLE_MEETING);
				onCreate(db);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// =========================================================================
	// Download相关的函数
	// =========================================================================
	public boolean insertDownloadInfo(String strAppKey, String strAppTitle,
			String strFileName, long nFileSize, long nDownSize,
			String strFileUrl, String strLocalPath, int nStatus) {

		ContentValues initialValues = new ContentValues();
		initialValues.put(DOWNLOAD_KEY_APPKEY, strAppKey);
		initialValues.put(DOWNLOAD_KEY_APPTITLE, strAppTitle);
		initialValues.put(DOWNLOAD_KEY_FILENAME, strFileName);
		initialValues.put(DOWNLOAD_KEY_FILESIZE, nFileSize);
		initialValues.put(DOWNLOAD_KEY_DOWNLOADED_SIZE, nDownSize);
		initialValues.put(DOWNLOAD_KEY_DOWNDATE, System.currentTimeMillis());

		initialValues.put(DOWNLOAD_KEY_FILEURL, strFileUrl);
		initialValues.put(DOWNLOAD_KEY_FILEPATH, strLocalPath);
		initialValues.put(DOWNLOAD_KEY_STATUS, nStatus);

		boolean bSuccess = true;
		try {
			bSuccess = db.insert(TABLE_DOWNLOAD, null, initialValues) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return bSuccess;
	}

	public boolean deleteDownloadInfo(String strAppKey) {
		boolean bSuccess = true;
		try {
			bSuccess = db.delete(TABLE_DOWNLOAD, DOWNLOAD_KEY_APPKEY + "=\""
					+ strAppKey + "\"", null) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return bSuccess;
	}

	public boolean updateDownloadInfo(String strAppKey, String strAppTitle,
			String strFileName, long nFileSize, long nDownSize,
			String strFileUrl, String strLocalPath, int nStatus) {
		if( db.isOpen() == false)
			return false;

		ContentValues initialValues = new ContentValues();
		initialValues.put(DOWNLOAD_KEY_APPKEY, strAppKey);
		initialValues.put(DOWNLOAD_KEY_APPTITLE, strAppTitle);
		initialValues.put(DOWNLOAD_KEY_FILENAME, strFileName);
		initialValues.put(DOWNLOAD_KEY_FILESIZE, nFileSize);
		initialValues.put(DOWNLOAD_KEY_DOWNLOADED_SIZE, nDownSize);
		initialValues.put(DOWNLOAD_KEY_DOWNDATE, System.currentTimeMillis());
		initialValues.put(DOWNLOAD_KEY_FILEURL, strFileUrl);
		initialValues.put(DOWNLOAD_KEY_FILEPATH, strLocalPath);
		initialValues.put(DOWNLOAD_KEY_STATUS, nStatus);

		boolean bSuccess = true;
		;
		try {
			bSuccess = db.update(TABLE_DOWNLOAD, initialValues,
					DOWNLOAD_KEY_FILENAME + "=\"" + strFileName + "\"", null) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	// 更新某个应用已经下载的字节数目
	public boolean updateDownloadInfo(String strAppKey, long nDownSize) {
		if( db.isOpen() == false)
			return false;

		ContentValues initialValues = new ContentValues();
		initialValues.put(DOWNLOAD_KEY_DOWNLOADED_SIZE, nDownSize);

		boolean bSuccess = true;

		try {
			bSuccess = db.update(TABLE_DOWNLOAD, initialValues,
					DOWNLOAD_KEY_APPKEY + "=\"" + strAppKey + "\"", null) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public boolean addDownloadInfo(String strAppKey, String strAppTitle,
			String strFileName, int nFileSize, int nDownSize,
			String strFileUrl, String strLocalPath, int nStatus) {
		if( db.isOpen() == false)
			return false;

		boolean bSuccess = true;
		Cursor mCursor = null;
		try {
			mCursor = this.db.query(TABLE_DOWNLOAD, DOWNLOAD_COLS,
					DOWNLOAD_KEY_APPKEY + "=\"" + strAppKey + "\"", null, null,
					null, null, null);
			if (mCursor.getCount() > 0) {
				this.updateDownloadInfo(strAppTitle, strAppKey, strFileName,
						nFileSize, nDownSize, strFileUrl, strLocalPath, nStatus);

			} else {
				this.insertDownloadInfo(strAppTitle, strAppKey, strFileName,
						nFileSize, nDownSize, strFileUrl, strLocalPath, nStatus);

			}
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		} finally {
			if (mCursor != null && !mCursor.isClosed()) {
				mCursor.close();
			}
		}

		return bSuccess;
	}

	public boolean addDownloadInfo(DownloadInfo data) {
		if( db.isOpen() == false)
			return false;

		if (data == null)
			return false;

		boolean bSuccess = true;
		Cursor mCursor = null;
		try {
			mCursor = this.db.query(TABLE_DOWNLOAD, DOWNLOAD_COLS,
					DOWNLOAD_KEY_FILENAME + "=\"" + data.strFileName + "\"",
					null, null, null, null, null);
			if (mCursor.getCount() > 0) {
				this.updateDownloadInfo(data.strAppKey, data.strFileTitle,
						data.strFileName, data.nFileSize,
						data.nFileDownloadedSize, data.strFileUrl,
						data.strLocalPath, data.nStatus);
			} else {
				this.insertDownloadInfo(data.strAppKey, data.strFileTitle,
						data.strFileName, data.nFileSize,
						data.nFileDownloadedSize, data.strFileUrl,
						data.strLocalPath, data.nStatus);

			}
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;

		} finally {
			if (mCursor != null && !mCursor.isClosed()) {
				mCursor.close();
			}
		}

		return bSuccess;
	}

	public DownloadInfoList getAllDownloadList() {

		Cursor accountCur = db.query(TABLE_DOWNLOAD, DOWNLOAD_COLS, null, null,
				null, null, null);
		if (accountCur == null)
			return null;

		DownloadInfoList infoList = new DownloadInfoList();
		if (accountCur.moveToFirst()) {

			do {
				DownloadInfo data = new DownloadInfo();

				int nColumn = accountCur.getColumnIndex(DOWNLOAD_KEY_APPKEY);
				data.strAppKey = accountCur.getString(nColumn);
				if (data.strAppKey == null)
					data.strAppKey = "";

				nColumn = accountCur.getColumnIndex(DOWNLOAD_KEY_APPTITLE);
				data.strFileTitle = accountCur.getString(nColumn);
				if (data.strFileTitle == null)
					data.strFileName = "";

				nColumn = accountCur.getColumnIndex(DOWNLOAD_KEY_FILENAME);
				data.strFileName = accountCur.getString(nColumn);
				if (data.strFileName == null)
					data.strFileName = "";

				nColumn = accountCur.getColumnIndex(DOWNLOAD_KEY_FILESIZE);
				data.nFileSize = accountCur.getInt(nColumn);

				nColumn = accountCur
						.getColumnIndex(DOWNLOAD_KEY_DOWNLOADED_SIZE);
				data.nFileDownloadedSize = accountCur.getInt(nColumn);

				nColumn = accountCur.getColumnIndex(DOWNLOAD_KEY_FILEURL);
				data.strFileUrl = accountCur.getString(nColumn);
				if (data.strFileUrl == null)
					data.strFileUrl = "";

				nColumn = accountCur.getColumnIndex(DOWNLOAD_KEY_FILEPATH);
				data.strLocalPath = accountCur.getString(nColumn);
				if (data.strLocalPath == null)
					data.strLocalPath = "";

				nColumn = accountCur.getColumnIndex(DOWNLOAD_KEY_DOWNDATE);
				data.nDownloadTime = accountCur.getLong(nColumn);

				nColumn = accountCur.getColumnIndex(DOWNLOAD_KEY_STATUS);
				data.nStatus = accountCur.getInt(nColumn);
				if (data.nStatus == DownloadInfo.DOWNLOAD_STATUS_RUNNING) {
					data.nStatus = DownloadInfo.DOWNLOAD_STATUS_CANCEL;
				}

				infoList.addDownloadInfo(data);

			} while (accountCur.moveToNext());
		}

		if (accountCur != null && !accountCur.isClosed())
			accountCur.close();

		return infoList;
	}

	// =========================================================================
	// Image相关的函数
	// =========================================================================
	public boolean insertImageInfo(String strImageUrl, Bitmap bm) {
		if( db.isOpen() == false)
			return false;

		ByteArrayOutputStream bitmapBlob = new ByteArrayOutputStream();
		bm.compress(Bitmap.CompressFormat.PNG, 100, bitmapBlob);

		ContentValues initialValues = new ContentValues();
		initialValues.put(IMAGE_KEY_URL, strImageUrl);
		initialValues.put(IMAGE_KEY_IMAGEBLOB, bitmapBlob.toByteArray());
		initialValues.put(IMAGE_KEY_IMAGESIZE, bitmapBlob.size());

		boolean bSuccess = true;
		try {
			bSuccess = db.insert(TABLE_IMAGE, null, initialValues) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public boolean deleteImageInfo(String strUrl) {
		if( db.isOpen() == false)
			return false;

		boolean bSuccess = true;
		try {
			bSuccess = db.delete(TABLE_IMAGE, IMAGE_KEY_URL + "=\"" + strUrl
					+ "\"", null) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public boolean updateImageInfo(String strUrl, Bitmap bm) {
		if( db.isOpen() == false)
			return false;

		ByteArrayOutputStream bitmapBlob = new ByteArrayOutputStream();
		bm.compress(Bitmap.CompressFormat.PNG, 100, bitmapBlob);

		ContentValues initialValues = new ContentValues();
		initialValues.put(IMAGE_KEY_URL, strUrl);
		initialValues.put(IMAGE_KEY_IMAGEBLOB, bitmapBlob.toByteArray());
		initialValues.put(IMAGE_KEY_IMAGESIZE, bitmapBlob.size());

		boolean bSuccess = true;
		try {
			bSuccess = db.update(TABLE_IMAGE, initialValues, IMAGE_KEY_URL
					+ "=\"" + strUrl + "\"", null) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public boolean addImageInfo(String strUrl, Bitmap bm) {
		if( db.isOpen() == false)
			return false;

		boolean bSuccess = true;
		Cursor mCursor = null;
		try {
			mCursor = this.db.query(TABLE_IMAGE, IMAGE_COLS, IMAGE_KEY_URL
					+ "=\"" + strUrl + "\"", null, null, null, null, null);
			if (mCursor.getCount() > 0) {
				this.updateImageInfo(strUrl, bm);
			} else {
				this.insertImageInfo(strUrl, bm);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		} finally {
			if (mCursor != null && !mCursor.isClosed()) {
				mCursor.close();
			}
		}

		return bSuccess;
	}

	public Bitmap getImageInfo(String strUrl) {
		if( db.isOpen() == false)
			return null;

		Cursor imageCur = db.query(TABLE_IMAGE, IMAGE_COLS, IMAGE_KEY_URL
				+ "=\"" + strUrl + "\"", null, null, null, null);
		if (imageCur == null)
			return null;

		Bitmap bmp = null;
		if (imageCur.moveToFirst()) {
			int nColumn = imageCur.getColumnIndex(IMAGE_KEY_IMAGEBLOB);
			byte[] blob = imageCur.getBlob(nColumn);
			bmp = BitmapFactory.decodeByteArray(blob, 0, blob.length);
		}

		if (imageCur != null && !imageCur.isClosed())
			imageCur.close();

		return bmp;
	}

	// =========================================================================
	// Account相关的函数
	// =========================================================================
	public boolean insertAccount(int nMainUserId, UserInfo info) {
		if( db.isOpen() == false)
			return false;

		if (info == null)
			return false;

		ContentValues initialValues = new ContentValues();
		initialValues.put(ACCOUNT_KEY_ID, info.nUserId);
		initialValues.put(ACCOUNT_KEY_MAIN_ID, nMainUserId);
		initialValues.put(ACCOUNT_KEY_PASSWORD, info.strPassword);
		initialValues.put(ACCOUNT_KEY_REALNAME, info.strRealName);
		initialValues.put(ACCOUNT_KEY_NICKNAME, info.strNickName);
		initialValues.put(ACCOUNT_KEY_EMAIL, info.strEmail);
		initialValues.put(ACCOUNT_KEY_MOBILE, info.strMobile);
		initialValues.put(ACCOUNT_KEY_ADDRESS, info.strAddress);
		initialValues.put(ACCOUNT_KEY_COMPANY, info.strCompany);
		initialValues.put(ACCOUNT_KEY_GENDER, info.bMale == true ? 1 : 0);
		initialValues.put(ACCOUNT_KEY_TRADE, info.nIndustry);
		initialValues.put(ACCOUNT_KEY_DEPT, info.nDepartment);
		initialValues.put(ACCOUNT_KEY_POSSTATUS, info.nPosStatus);
		initialValues.put(ACCOUNT_KEY_COIN, info.nCoin);
		initialValues.put(ACCOUNT_KEY_CHARGE, info.fCharge);
		initialValues.put(ACCOUNT_KEY_LASTLOGIN, info.strLastLoginTime);

		boolean bSuccess = true;
		try {
			bSuccess = db.insert(TABLE_ACCOUNT, null, initialValues) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	/**
	 * 插入主帐号
	 * 
	 * @param info
	 * @return
	 */
	public boolean insertMainAccount(UserInfo info) {
		if( db.isOpen() == false)
			return false;

		ContentValues initialValues = new ContentValues();
		initialValues.put(ACCOUNT_KEY_ID, info.nUserId);
		initialValues.put(ACCOUNT_KEY_MAIN_ID, 0);
		initialValues.put(ACCOUNT_KEY_PASSWORD, info.strPassword);
		initialValues.put(ACCOUNT_KEY_REALNAME, info.strRealName);
		initialValues.put(ACCOUNT_KEY_NICKNAME, info.strNickName);
		initialValues.put(ACCOUNT_KEY_EMAIL, info.strEmail);
		initialValues.put(ACCOUNT_KEY_MOBILE, info.strMobile);
		initialValues.put(ACCOUNT_KEY_ADDRESS, info.strAddress);
		initialValues.put(ACCOUNT_KEY_COMPANY, info.strCompany);
		initialValues.put(ACCOUNT_KEY_GENDER, info.bMale == true ? 1 : 0);
		initialValues.put(ACCOUNT_KEY_TRADE, info.nIndustry);
		initialValues.put(ACCOUNT_KEY_DEPT, info.nDepartment);
		initialValues.put(ACCOUNT_KEY_POSSTATUS, info.nPosStatus);
		initialValues.put(ACCOUNT_KEY_COIN, info.nCoin);
		initialValues.put(ACCOUNT_KEY_CHARGE, info.fCharge);
		initialValues.put(ACCOUNT_KEY_LASTLOGIN, info.strLastLoginTime);

		boolean bSuccess = true;
		try {
			bSuccess = db.insert(TABLE_ACCOUNT, null, initialValues) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public boolean deleteAccount(int nMainUserId, int nUserId) {
		if( db.isOpen() == false)
			return false;

		boolean bSuccess = true;
		try {
			String strCondition = ACCOUNT_KEY_ID + "=" + nUserId + " AND "
					+ ACCOUNT_KEY_MAIN_ID + "=" + nMainUserId;
			bSuccess = db.delete(TABLE_ACCOUNT, strCondition, null) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public boolean deleteAccount(int nMainUserId) {
		if( db.isOpen() == false)
			return false;

		boolean bSuccess = true;
		try {
			// 先删除主帐号本身
			String strCondition = ACCOUNT_KEY_ID + "=" + nMainUserId;
			bSuccess = db.delete(TABLE_ACCOUNT, strCondition, null) > 0;

			// 删除关联帐号
			strCondition = ACCOUNT_KEY_MAIN_ID + "=" + nMainUserId;
			bSuccess = db.delete(TABLE_ACCOUNT, strCondition, null) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public boolean updateAccount(int nMainUserId, UserInfo info) {
		if( db.isOpen() == false)
			return false;

		if (info == null)
			return false;

		ContentValues initialValues = new ContentValues();
		initialValues.put(ACCOUNT_KEY_ID, info.nUserId);
		initialValues.put(ACCOUNT_KEY_MAIN_ID, nMainUserId);
		initialValues.put(ACCOUNT_KEY_PASSWORD, info.strPassword);
		initialValues.put(ACCOUNT_KEY_REALNAME, info.strRealName);
		initialValues.put(ACCOUNT_KEY_NICKNAME, info.strNickName);
		initialValues.put(ACCOUNT_KEY_EMAIL, info.strEmail);
		initialValues.put(ACCOUNT_KEY_MOBILE, info.strMobile);
		initialValues.put(ACCOUNT_KEY_ADDRESS, info.strAddress);
		initialValues.put(ACCOUNT_KEY_COMPANY, info.strCompany);
		initialValues.put(ACCOUNT_KEY_GENDER, info.bMale == true ? 1 : 0);
		initialValues.put(ACCOUNT_KEY_TRADE, info.nIndustry);
		initialValues.put(ACCOUNT_KEY_DEPT, info.nDepartment);
		initialValues.put(ACCOUNT_KEY_POSSTATUS, info.nPosStatus);
		initialValues.put(ACCOUNT_KEY_COIN, info.nCoin);
		initialValues.put(ACCOUNT_KEY_CHARGE, info.fCharge);
		initialValues.put(ACCOUNT_KEY_LASTLOGIN, info.strLastLoginTime);

		boolean bSuccess = true;
		try {
			String strCondition = ACCOUNT_KEY_ID + "=" + info.nUserId + " AND "
					+ ACCOUNT_KEY_MAIN_ID + "=" + nMainUserId;
			bSuccess = db.update(TABLE_ACCOUNT, initialValues, strCondition,
					null) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public boolean updateMainAccount(UserInfo info) {
		if( db.isOpen() == false)
			return false;

		if (info == null)
			return false;

		ContentValues initialValues = new ContentValues();
		initialValues.put(ACCOUNT_KEY_ID, info.nUserId);
		initialValues.put(ACCOUNT_KEY_MAIN_ID, 0);
		initialValues.put(ACCOUNT_KEY_PASSWORD, info.strPassword);
		initialValues.put(ACCOUNT_KEY_REALNAME, info.strRealName);
		initialValues.put(ACCOUNT_KEY_NICKNAME, info.strNickName);
		initialValues.put(ACCOUNT_KEY_EMAIL, info.strEmail);
		initialValues.put(ACCOUNT_KEY_MOBILE, info.strMobile);
		initialValues.put(ACCOUNT_KEY_ADDRESS, info.strAddress);
		initialValues.put(ACCOUNT_KEY_COMPANY, info.strCompany);
		initialValues.put(ACCOUNT_KEY_GENDER, info.bMale == true ? 1 : 0);
		initialValues.put(ACCOUNT_KEY_TRADE, info.nIndustry);
		initialValues.put(ACCOUNT_KEY_DEPT, info.nDepartment);
		initialValues.put(ACCOUNT_KEY_POSSTATUS, info.nPosStatus);
		initialValues.put(ACCOUNT_KEY_COIN, info.nCoin);
		initialValues.put(ACCOUNT_KEY_CHARGE, info.fCharge);
		initialValues.put(ACCOUNT_KEY_LASTLOGIN, info.strLastLoginTime);

		boolean bSuccess = true;
		try {
			String strCondition = ACCOUNT_KEY_ID + "=" + info.nUserId + " AND "
					+ ACCOUNT_KEY_MAIN_ID + "=" + 0;
			bSuccess = db.update(TABLE_ACCOUNT, initialValues, strCondition,
					null) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public boolean addAccount(int nMainUserId, UserInfo info) {
		if( db.isOpen() == false)
			return false;

		if (info == null)
			return false;

		boolean bSuccess = true;

		Cursor mCursor = null;
		try {
			String strCondition = ACCOUNT_KEY_ID + "=" + info.nUserId + " AND "
					+ ACCOUNT_KEY_MAIN_ID + "=" + nMainUserId;
			mCursor = this.db.query(TABLE_ACCOUNT, ACCOUNT_COLS, strCondition,
					null, null, null, null, null);
			if (mCursor.getCount() > 0) {
				this.updateAccount(nMainUserId, info);
			} else {
				this.insertAccount(nMainUserId, info);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		} finally {
			if (mCursor != null && !mCursor.isClosed()) {
				mCursor.close();
			}
		}

		return bSuccess;
	}

	public boolean addMainAccount(UserInfo info) {
		if( db.isOpen() == false)
			return false;

		if (info == null)
			return false;

		boolean bSuccess = true;

		Cursor mCursor = null;
		try {
			String strCondition = ACCOUNT_KEY_ID + "=" + info.nUserId + " AND "
					+ ACCOUNT_KEY_MAIN_ID + "=" + 0;
			mCursor = this.db.query(TABLE_ACCOUNT, ACCOUNT_COLS, strCondition,
					null, null, null, null, null);
			if (mCursor.getCount() > 0) {
				this.updateMainAccount(info);
			} else {
				this.insertMainAccount(info);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		} finally {
			if (mCursor != null && !mCursor.isClosed()) {
				mCursor.close();
			}
		}

		return bSuccess;
	}

	private UserInfo getUserInfoFromCursor(Cursor accountCur) {
		if (accountCur == null)
			return null;

		UserInfo data = new UserInfo();

		int nColumn = accountCur.getColumnIndex(ACCOUNT_KEY_ID);
		data.nUserId = accountCur.getInt(nColumn);

		nColumn = accountCur.getColumnIndex(ACCOUNT_KEY_PASSWORD);
		data.strPassword = accountCur.getString(nColumn);

		nColumn = accountCur.getColumnIndex(ACCOUNT_KEY_REALNAME);
		data.strRealName = accountCur.getString(nColumn);

		nColumn = accountCur.getColumnIndex(ACCOUNT_KEY_NICKNAME);
		data.strNickName = accountCur.getString(nColumn);

		nColumn = accountCur.getColumnIndex(ACCOUNT_KEY_EMAIL);
		data.strEmail = accountCur.getString(nColumn);

		nColumn = accountCur.getColumnIndex(ACCOUNT_KEY_MOBILE);
		data.strMobile = accountCur.getString(nColumn);

		nColumn = accountCur.getColumnIndex(ACCOUNT_KEY_ADDRESS);
		data.strAddress = accountCur.getString(nColumn);

		nColumn = accountCur.getColumnIndex(ACCOUNT_KEY_COMPANY);
		data.strCompany = accountCur.getString(nColumn);

		nColumn = accountCur.getColumnIndex(ACCOUNT_KEY_GENDER);
		data.bMale = accountCur.getInt(nColumn) == 1 ? true : false;

		nColumn = accountCur.getColumnIndex(ACCOUNT_KEY_COIN);
		data.nCoin = accountCur.getInt(nColumn);

		nColumn = accountCur.getColumnIndex(ACCOUNT_KEY_TRADE);
		data.nIndustry = accountCur.getInt(nColumn);

		nColumn = accountCur.getColumnIndex(ACCOUNT_KEY_DEPT);
		data.nDepartment = accountCur.getInt(nColumn);

		nColumn = accountCur.getColumnIndex(ACCOUNT_KEY_POSSTATUS);
		data.nPosStatus = accountCur.getInt(nColumn);

		nColumn = accountCur.getColumnIndex(ACCOUNT_KEY_COIN);
		data.nCoin = accountCur.getInt(nColumn);

		nColumn = accountCur.getColumnIndex(ACCOUNT_KEY_CHARGE);
		data.fCharge = accountCur.getFloat(nColumn);

		nColumn = accountCur.getColumnIndex(ACCOUNT_KEY_LASTLOGIN);
		data.strLastLoginTime = accountCur.getString(nColumn);

		return data;
	}

	/**
	 * @desc 获取某个用户关联的所有的帐号
	 * @param nUserId
	 * @return
	 */
	public UserInfoList getAccount(int nUserId) {

		String strCondition = ACCOUNT_KEY_MAIN_ID + "=" + nUserId + " OR "
				+ "(" + ACCOUNT_KEY_ID + "=" + nUserId + " AND "
				+ ACCOUNT_KEY_MAIN_ID + "=0)";
		Cursor accountCur = db.query(TABLE_ACCOUNT, ACCOUNT_COLS, strCondition,
				null, null, null, null);
		if (accountCur == null)
			return null;

		UserInfoList infoList = new UserInfoList();
		if (accountCur.moveToFirst()) {

			do {
				UserInfo data = getUserInfoFromCursor(accountCur);
				infoList.addUserInfo(data);

			} while (accountCur.moveToNext());
		}

		if (accountCur != null && !accountCur.isClosed())
			accountCur.close();

		return infoList;
	}

	/**
	 * @desc 获取某个用户关联的所有的帐号
	 * @param nUserId
	 * @return
	 */
	public UserInfo getAccount(int nMainUserId, int nUserId) {

		String strCondition = ACCOUNT_KEY_ID + "=" + nUserId + " AND "
				+ ACCOUNT_KEY_MAIN_ID + "=" + nMainUserId;
		Cursor accountCur = db.query(TABLE_ACCOUNT, ACCOUNT_COLS, strCondition,
				null, null, null, null);
		if (accountCur == null)
			return null;

		UserInfo data = null;

		if (accountCur.moveToFirst()) {
			data = getUserInfoFromCursor(accountCur);
		}

		if (accountCur != null && !accountCur.isClosed())
			accountCur.close();

		return data;
	}

	/**
	 * @desc 获取某个用户关联的所有的帐号
	 * @param nUserId
	 * @return
	 */
	public UserInfo getMainAccount(int nMainUserId) {

		String strCondition = ACCOUNT_KEY_ID + "=" + nMainUserId + " AND "
				+ ACCOUNT_KEY_MAIN_ID + "=" + 0;
		Cursor accountCur = db.query(TABLE_ACCOUNT, ACCOUNT_COLS, strCondition,
				null, null, null, null);
		if (accountCur == null)
			return null;

		UserInfo data = null;

		if (accountCur.moveToFirst()) {
			data = getUserInfoFromCursor(accountCur);
		}

		if (accountCur != null && !accountCur.isClosed())
			accountCur.close();

		return data;
	}

	// =========================================================================
	// Trades相关的函数
	// =========================================================================
	public boolean insertTrades(int nId, String strName) {

		ContentValues initialValues = new ContentValues();
		initialValues.put(TRADE_KEY_ID, nId);
		initialValues.put(TRADE_KEY_NAME, strName);

		boolean bSuccess = true;
		try {
			bSuccess = db.insert(TABLE_TRADE, null, initialValues) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public boolean deleteTrades(int nId) {
		boolean bSuccess = true;
		try {
			String strCondition = TRADE_KEY_ID + "=" + nId;
			bSuccess = db.delete(TABLE_TRADE, strCondition, null) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public boolean deleteAllTrades() {
		boolean bSuccess = true;
		;
		try {
			bSuccess = db.delete(TABLE_TRADE, null, null) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public MeetingTradesInfo getTrades(int nId) {

		String strCondition = TRADE_KEY_ID + "=" + nId;
		Cursor accountCur = db.query(TABLE_TRADE, TRADE_COLS, strCondition,
				null, null, null, null);
		if (accountCur == null)
			return null;

		MeetingTradesInfo data = new MeetingTradesInfo();

		int nColumn = accountCur.getColumnIndex(TRADE_KEY_ID);
		data.nCategoryId = accountCur.getInt(nColumn);

		nColumn = accountCur.getColumnIndex(TRADE_KEY_NAME);
		data.strCategoryName = accountCur.getString(nColumn);

		if (accountCur != null && !accountCur.isClosed())
			accountCur.close();

		return data;
	}

	public TradesInfoList getAllTrades() {

		Cursor accountCur = db.query(TABLE_TRADE, TRADE_COLS, null, null, null,
				null, null);
		if (accountCur == null)
			return null;

		TradesInfoList tradesList = new TradesInfoList();
		if (accountCur.moveToFirst()) {

			do {
				MeetingTradesInfo data = new MeetingTradesInfo();

				int nColumn = accountCur.getColumnIndex(TRADE_KEY_ID);
				data.nCategoryId = accountCur.getInt(nColumn);

				nColumn = accountCur.getColumnIndex(TRADE_KEY_NAME);
				data.strCategoryName = accountCur.getString(nColumn);

				tradesList.addMeetingTradesInfo(data);

			} while (accountCur.moveToNext());
		}

		if (accountCur != null && !accountCur.isClosed())
			accountCur.close();

		return tradesList;

	}

	// =========================================================================
	// City相关的函数
	// =========================================================================
	public boolean insertCity(CityInfo cityInfo) {
		if (cityInfo == null)
			return false;

		ContentValues initialValues = new ContentValues();
		initialValues.put(CITY_KEY_ID, cityInfo.nId);
		initialValues.put(CITY_KEY_PROVINCEID, cityInfo.nProvinceId);
		initialValues.put(CITY_KEY_CITYID, cityInfo.nCityId);
		initialValues.put(CITY_KEY_NAME, cityInfo.strCityName);

		boolean bSuccess = true;

		try {
			bSuccess = db.insert(TABLE_CITY, null, initialValues) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public boolean updateCity(CityInfo cityInfo) {
		if (cityInfo == null)
			return false;

		ContentValues initialValues = new ContentValues();
		initialValues.put(CITY_KEY_ID, cityInfo.nId);
		initialValues.put(CITY_KEY_PROVINCEID, cityInfo.nProvinceId);
		initialValues.put(CITY_KEY_CITYID, cityInfo.nCityId);
		initialValues.put(CITY_KEY_NAME, cityInfo.strCityName);

		boolean bSuccess = true;

		try {
			String strCondition = CITY_KEY_ID + "=" + cityInfo.nId;
			bSuccess = db.update(TABLE_CITY, initialValues, strCondition, null) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public boolean addCity(CityInfo cityInfo) {
		if (cityInfo == null)
			return false;

		boolean bSuccess = true;

		Cursor mCursor = null;
		try {
			String strCondition = CITY_KEY_ID + "=" + cityInfo.nId;
			mCursor = this.db.query(TABLE_CITY, CITY_COLS, strCondition, null,
					null, null, null, null);
			if (mCursor.getCount() > 0) {
				this.updateCity(cityInfo);
			} else {
				this.insertCity(cityInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		} finally {
			if (mCursor != null && !mCursor.isClosed()) {
				mCursor.close();
			}
		}

		return bSuccess;
	}

	public boolean deleteCity(String strName) {
		boolean bSuccess = true;
		try {
			String strCondition = CITY_KEY_NAME + "=" + strName;
			bSuccess = db.delete(TABLE_CITY, strCondition, null) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public CityInfoList getAllCitys() {

		Cursor accountCur = db.query(TABLE_CITY, CITY_COLS, null, null, null,
				null, null);
		if (accountCur == null)
			return null;

		CityInfoList infoList = new CityInfoList();
		if (accountCur.moveToFirst()) {

			do {
				CityInfo info = new CityInfo();

				int nColumn = accountCur.getColumnIndex(CITY_KEY_ID);
				info.nId = accountCur.getInt(nColumn);

				nColumn = accountCur.getColumnIndex(CITY_KEY_NAME);
				info.strCityName = accountCur.getString(nColumn);

				nColumn = accountCur.getColumnIndex(CITY_KEY_PROVINCEID);
				info.nProvinceId = accountCur.getInt(nColumn);

				nColumn = accountCur.getColumnIndex(CITY_KEY_CITYID);
				info.nCityId = accountCur.getInt(nColumn);

				infoList.addCityInfo(info);

			} while (accountCur.moveToNext());
		}

		if (accountCur != null && !accountCur.isClosed())
			accountCur.close();

		return infoList;

	}

	// =========================================================================
	// 职位相关的函数
	// =========================================================================
	public boolean insertPos(int nId, String strName) {

		ContentValues initialValues = new ContentValues();
		initialValues.put(POS_KEY_ID, nId);
		initialValues.put(POS_KEY_NAME, strName);

		boolean bSuccess = true;
		try {
			bSuccess = db.insert(TABLE_POS, null, initialValues) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public boolean deleteAllPos() {
		boolean bSuccess = true;

		try {
			bSuccess = db.delete(TABLE_POS, null, null) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public ArrayList<PosInfo> getAllPos() {

		Cursor posCur = db.query(TABLE_POS, POS_COLS, null, null, null, null,
				null);
		if (posCur == null)
			return null;

		ArrayList<PosInfo> infoList = new ArrayList<PosInfo>();
		if (posCur.moveToFirst()) {

			do {

				PosInfo info = new PosInfo();

				int nColumn = posCur.getColumnIndex(POS_KEY_ID);
				info.nId = posCur.getInt(nColumn);

				nColumn = posCur.getColumnIndex(POS_KEY_NAME);
				info.strPosName = posCur.getString(nColumn);

				infoList.add(info);

			} while (posCur.moveToNext());
		}

		if (posCur != null && !posCur.isClosed())
			posCur.close();

		return infoList;

	}

	// =========================================================================
	// 部门相关的函数
	// =========================================================================
	public boolean insertDept(int nId, String strName) {

		ContentValues initialValues = new ContentValues();
		initialValues.put(DEPT_KEY_ID, nId);
		initialValues.put(DEPT_KEY_NAME, strName);

		boolean bSuccess = true;
		try {
			bSuccess = db.insert(TABLE_DEPT, null, initialValues) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public boolean deleteAllDepts() {
		boolean bSuccess = true;
		try {
			bSuccess = db.delete(TABLE_DEPT, null, null) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public ArrayList<DepartmentInfo> getAllDepts() {

		Cursor deptCur = db.query(TABLE_DEPT, DEPT_COLS, null, null, null,
				null, null);
		if (deptCur == null)
			return null;

		ArrayList<DepartmentInfo> infoList = new ArrayList<DepartmentInfo>();
		if (deptCur.moveToFirst()) {

			do {

				DepartmentInfo info = new DepartmentInfo();

				int nColumn = deptCur.getColumnIndex(DEPT_KEY_ID);
				info.nId = deptCur.getInt(nColumn);

				nColumn = deptCur.getColumnIndex(DEPT_KEY_NAME);
				String strName = deptCur.getString(nColumn);
				info.strDepartmentName = strName;

				infoList.add(info);

			} while (deptCur.moveToNext());
		}

		if (deptCur != null && !deptCur.isClosed())
			deptCur.close();

		return infoList;
	}

	// =========================================================================
	// Meeting相关的函数
	// =========================================================================
	public boolean insertMeeting(FullMeetingInfo fullInfo) {
		if (fullInfo == null)
			return false;

		ContentValues initialValues = new ContentValues();
		initialValues.put(MEETING_KEY_ID, fullInfo.nId);
		initialValues.put(MEETING_KEY_TITLE, fullInfo.strTitle);
		initialValues.put(MEETING_KEY_LOGO, fullInfo.strLogoUrl);
		initialValues.put(MEETING_KEY_ADDRESS, fullInfo.strAddress);
		initialValues.put(MEETING_KEY_BEGINTIME, fullInfo.strBeingTime);
		initialValues.put(MEETING_KEY_ENDTIME, fullInfo.strEndTime);
		initialValues.put(MEETING_KEY_SUMMARY, fullInfo.strSummary);
		initialValues.put(MEETING_KEY_TYPE, fullInfo.strEventTypeName);
		initialValues.put(MEETING_KEY_JONILIMITTIME, fullInfo.strJoinLimitTime);
		initialValues.put(MEETING_KEY_PRICE, fullInfo.lPrice);
		initialValues.put(MEETING_KEY_LATITUTE, fullInfo.fLatitude);
		initialValues.put(MEETING_KEY_LONGITUTE, fullInfo.fLongitude);
		initialValues.put(MEETING_KEY_ISCLOSETOPIC,
				fullInfo.bCloseTopic == true ? 1 : 0);
		initialValues.put(MEETING_KEY_ISCLOSEJOINE,
				fullInfo.bCloseJoin == true ? 1 : 0);
		initialValues.put(MEETING_KEY_ISJOINED, fullInfo.bIsJoined == true ? 1
				: 0);
		initialValues.put(MEETING_KEY_ISFAVED, fullInfo.bIsFaved == true ? 1
				: 0);
		initialValues.put(MEETING_KEY_DOCUMENTS, fullInfo.nDocuments);
		initialValues.put(MEETING_KEY_VISIT_TIME, 0);

		boolean bSuccess = true;
		try {
			bSuccess = db.insert(TABLE_MEETING, null, initialValues) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public boolean deleteMeeting(int nMeetingId) {
		boolean bSuccess = true;

		try {
			String strCondition = MEETING_KEY_ID + "=" + nMeetingId;
			bSuccess = db.delete(TABLE_MEETING, strCondition, null) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public boolean updateMeeting(FullMeetingInfo fullInfo) {
		if (fullInfo == null)
			return false;

		ContentValues initialValues = new ContentValues();
		initialValues.put(MEETING_KEY_ID, fullInfo.nId);
		initialValues.put(MEETING_KEY_TITLE, fullInfo.strTitle);
		initialValues.put(MEETING_KEY_LOGO, fullInfo.strLogoUrl);
		initialValues.put(MEETING_KEY_ADDRESS, fullInfo.strAddress);
		initialValues.put(MEETING_KEY_BEGINTIME, fullInfo.strBeingTime);
		initialValues.put(MEETING_KEY_ENDTIME, fullInfo.strEndTime);
		initialValues.put(MEETING_KEY_SUMMARY, fullInfo.strSummary);
		initialValues.put(MEETING_KEY_TYPE, fullInfo.strEventTypeName);
		initialValues.put(MEETING_KEY_JONILIMITTIME, fullInfo.strJoinLimitTime);
		initialValues.put(MEETING_KEY_PRICE, fullInfo.lPrice);
		initialValues.put(MEETING_KEY_LATITUTE, fullInfo.fLatitude);
		initialValues.put(MEETING_KEY_LONGITUTE, fullInfo.fLongitude);
		initialValues.put(MEETING_KEY_ISCLOSETOPIC,
				fullInfo.bCloseTopic == true ? 1 : 0);
		initialValues.put(MEETING_KEY_ISCLOSEJOINE,
				fullInfo.bCloseJoin == true ? 1 : 0);
		initialValues.put(MEETING_KEY_ISJOINED, fullInfo.bIsJoined == true ? 1
				: 0);
		initialValues.put(MEETING_KEY_ISFAVED, fullInfo.bIsFaved == true ? 1
				: 0);
		initialValues.put(MEETING_KEY_DOCUMENTS, fullInfo.nDocuments);

		boolean bSuccess = true;

		try {
			String strCondition = MEETING_KEY_ID + "=" + fullInfo.nId;
			bSuccess = db.update(TABLE_MEETING, initialValues, strCondition,
					null) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public boolean updateMeetingVisitTime(int nMeetingId, long nTime) {

		ContentValues initialValues = new ContentValues();
		initialValues.put(MEETING_KEY_ID, nMeetingId);
		initialValues.put(MEETING_KEY_VISIT_TIME, nTime);

		boolean bSuccess = true;
		try {
			String strCondition = MEETING_KEY_ID + "=" + nMeetingId;
			bSuccess = db.update(TABLE_MEETING, initialValues, strCondition,
					null) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public boolean clearMeetingVisitTime() {

		ContentValues initialValues = new ContentValues();
		initialValues.put(MEETING_KEY_VISIT_TIME, 0);

		boolean bSuccess = true;
		try {
			bSuccess = db.update(TABLE_MEETING, initialValues, null, null) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		}

		return bSuccess;
	}

	public boolean addMeeting(FullMeetingInfo fullInfo) {
		if (fullInfo == null)
			return false;

		boolean bSuccess = true;

		Cursor mCursor = null;
		try {
			String strCondition = MEETING_KEY_ID + "=" + fullInfo.nId;
			mCursor = this.db.query(TABLE_MEETING, MEETING_COLS, strCondition,
					null, null, null, null, null);
			if (mCursor.getCount() > 0) {
				this.updateMeeting(fullInfo);
			} else {
				this.insertMeeting(fullInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			bSuccess = false;
		} finally {
			if (mCursor != null && !mCursor.isClosed()) {
				mCursor.close();
			}
		}

		return bSuccess;
	}

	private FullMeetingInfo getFullMeetingInfoFromCursor(Cursor meetingCur) {
		if (meetingCur == null)
			return null;

		FullMeetingInfo data = new FullMeetingInfo();

		int nColumn = meetingCur.getColumnIndex(MEETING_KEY_ID);
		data.nId = meetingCur.getInt(nColumn);

		nColumn = meetingCur.getColumnIndex(MEETING_KEY_TITLE);
		data.strTitle = meetingCur.getString(nColumn);

		nColumn = meetingCur.getColumnIndex(MEETING_KEY_LOGO);
		data.strLogoUrl = meetingCur.getString(nColumn);

		nColumn = meetingCur.getColumnIndex(MEETING_KEY_ADDRESS);
		data.strAddress = meetingCur.getString(nColumn);

		nColumn = meetingCur.getColumnIndex(MEETING_KEY_BEGINTIME);
		data.strBeingTime = meetingCur.getString(nColumn);

		nColumn = meetingCur.getColumnIndex(MEETING_KEY_ENDTIME);
		data.strEndTime = meetingCur.getString(nColumn);

		nColumn = meetingCur.getColumnIndex(MEETING_KEY_SUMMARY);
		data.strSummary = meetingCur.getString(nColumn);

		nColumn = meetingCur.getColumnIndex(MEETING_KEY_TYPE);
		data.strEventTypeName = meetingCur.getString(nColumn);

		nColumn = meetingCur.getColumnIndex(MEETING_KEY_JONILIMITTIME);
		data.strJoinLimitTime = meetingCur.getString(nColumn);

		nColumn = meetingCur.getColumnIndex(MEETING_KEY_PRICE);
		data.lPrice = (long) meetingCur.getFloat(nColumn);

		nColumn = meetingCur.getColumnIndex(MEETING_KEY_LATITUTE);
		data.fLatitude = (long) meetingCur.getFloat(nColumn);

		nColumn = meetingCur.getColumnIndex(MEETING_KEY_LONGITUTE);
		data.fLongitude = (long) meetingCur.getFloat(nColumn);

		nColumn = meetingCur.getColumnIndex(MEETING_KEY_ISCLOSETOPIC);
		data.bCloseTopic = (long) meetingCur.getInt(nColumn) == 1 ? true
				: false;

		nColumn = meetingCur.getColumnIndex(MEETING_KEY_ISCLOSEJOINE);
		data.bCloseJoin = (long) meetingCur.getInt(nColumn) == 1 ? true : false;

		nColumn = meetingCur.getColumnIndex(MEETING_KEY_ISJOINED);
		data.bIsJoined = (long) meetingCur.getInt(nColumn) == 1 ? true : false;

		nColumn = meetingCur.getColumnIndex(MEETING_KEY_ISFAVED);
		data.bIsFaved = (long) meetingCur.getInt(nColumn) == 1 ? true : false;

		nColumn = meetingCur.getColumnIndex(MEETING_KEY_DOCUMENTS);
		data.nDocuments = meetingCur.getInt(nColumn);

		return data;
	}

	public FullMeetingInfoList getAllMeeting() {

		Cursor meetingCur = db.query(TABLE_MEETING, MEETING_COLS, null, null,
				null, null, null);
		if (meetingCur == null)
			return null;

		FullMeetingInfoList infoList = new FullMeetingInfoList();
		if (meetingCur.moveToFirst()) {

			do {
				FullMeetingInfo data = getFullMeetingInfoFromCursor(meetingCur);
				if (data == null)
					continue;

				infoList.addFullMeetingInfo(data);

			} while (meetingCur.moveToNext());
		}

		if (meetingCur != null && !meetingCur.isClosed())
			meetingCur.close();

		return infoList;
	}

	public FullMeetingInfoList getPagesMeetings(int nPageSize, int nPages) {

		String sql = "select * from " + TABLE_MEETING + " where "
				+ MEETING_KEY_VISIT_TIME + "!= 0 Order by "
				+ MEETING_KEY_VISIT_TIME + " desc Limit "
				+ String.valueOf(nPageSize) + " Offset "
				+ String.valueOf(nPages * nPageSize);
		Cursor meetingCur = db.rawQuery(sql, null);
		if (meetingCur == null)
			return null;

		FullMeetingInfoList infoList = new FullMeetingInfoList();
		if (meetingCur.moveToFirst()) {

			do {
				FullMeetingInfo data = getFullMeetingInfoFromCursor(meetingCur);
				if (data == null)
					continue;

				infoList.addFullMeetingInfo(data);

			} while (meetingCur.moveToNext());
		}

		if (meetingCur != null && !meetingCur.isClosed())
			meetingCur.close();

		return infoList;
	}

	public FullMeetingInfo getMeetingById(int nMeetingId) {

		String strCondition = MEETING_KEY_ID + "=" + nMeetingId;
		Cursor meetingCur = db.query(TABLE_MEETING, MEETING_COLS, strCondition,
				null, null, null, null);
		if (meetingCur == null)
			return null;

		FullMeetingInfo data = null;
		if (meetingCur.moveToFirst()) {
			data = getFullMeetingInfoFromCursor(meetingCur);
		}

		if (meetingCur != null && !meetingCur.isClosed())
			meetingCur.close();

		return data;
	}
}